package com.hk.Mgr.web.controller;

import com.hk.Mgr.web.message.kyc.UserExtralReq;
import com.hk.Mgr.web.message.kyc.UserExtralRes;
import com.hk.app.utils.ExcelService;
import com.hk.base.BaseCtrl;
import com.hk.base.ResponseBase;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author Anti 个人统计报表
 */
@Controller
@RequestMapping(value = BaseCtrl.MgrRootPath + "/userStatisticsController")
public class UserStatisticsController extends BaseCtrl {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 个人统计分页统计
     *
     * @param req
     */
    @ResponseBody
    @RequestMapping(value = "/total", method = RequestMethod.POST)
    public ResponseBase<Boolean> total(@RequestBody UserExtralReq req) {
        ResponseBase<Boolean> resp = new ResponseBase<>();

        Map<String, Object> queryForMap = this.jdbcTemplate.queryForMap(
                buildCountSQL(req.getUserRealName(), req.getChannel()));
        Integer total = Integer.valueOf(queryForMap.get("total").toString()) ;


        resp.setResult(true);
        resp.setSize(total);

        return resp;
    }

    /**
     * 个人统计
     *
     * @param req
     */
    @ResponseBody
    @RequestMapping(value = "/list", method = RequestMethod.POST)
    public ResponseBase<List<UserExtralRes>> list(@RequestBody UserExtralReq req) {
        ResponseBase<List<UserExtralRes>> resp = new ResponseBase<List<UserExtralRes>>();

        Map<String, Object> queryForMap = this.jdbcTemplate.queryForMap(
                buildCountSQL(req.getUserRealName(), req.getChannel()));
        Integer total = Integer.valueOf(queryForMap.get("total").toString()) ;

        String sql = buildSelectSQL(req.getUserRealName(), req.getChannel(),req.getCurPage(),req.getPageSize());

        List<UserExtralRes> result = queryList(sql);

        resp.setResult(result);
        resp.setSize(total);

        return resp;
    }


    /**
     * 导出所有的数据
     */
    @RequestMapping(value = "/excel")
    @ResponseBody
    public void excelExport(HttpServletRequest request,
                            HttpServletResponse response, @RequestParam String userRealName,
                            @RequestParam Integer channel) {

        String sql = buildSelectSQL(userRealName, channel,null,null);
        List<UserExtralRes> result = queryList(sql);

        LinkedHashMap<String, String> struct = new LinkedHashMap<>();
        struct.put("userRealName", "姓名");
        struct.put("userPhoneNum", "手机号");
        struct.put("relationUserAmount", "客户数");
        struct.put("tieCardAmount", "绑卡客户数");
        struct.put("bespeakOrderAmount", "预约订单数");
        struct.put("payOrderAmount", "付款订单数");
        struct.put("payOrderMoney", "付款订总金额");
        struct.put("userDutName", "员工职务");
        struct.put("userDepName", "所属部门");

        ExcelService excelService = new ExcelService(result, struct);

        String title = "个人统计报表";
        excelService.exportToStream(response, title);
    }

    private List<UserExtralRes> queryList(String sql) {
        List<Map<String, Object>> userStats = this.jdbcTemplate.queryForList(sql);

        return userStats
                .parallelStream()
                .map(map ->{
                    UserExtralRes one = new UserExtralRes();

                    Object phoneNum = map.get("phoneNum");
                    if(phoneNum != null)
                        one.setUserPhoneNum(phoneNum.toString());

                    one.setUserRealName((String) map.get("userRealName"));
                    one.setUserDutName((String) map.get("userDuty"));
                    one.setUserDepName((String) map.get("userDepartment"));

                    one.setRelationUserAmount(Integer.valueOf(map.get("relationUserAmount").toString()));
                    one.setPayOrderAmount(Integer.valueOf(map.get("payOrderAmount").toString()));
                    one.setTieCardAmount(Integer.valueOf(map.get("tieCardAmount").toString()));

                    BigDecimal payOrderMoney = BigDecimal.ZERO;

                    if (map.get("payOrderMoney") != null)
                        payOrderMoney = (BigDecimal) map.get("payOrderMoney");

                    one.setPayOrderMoney(payOrderMoney);

                    return one;
                })
                .collect(Collectors.toList());
    }

    private String buildSelectSQL(String userRealName, Integer channel, Integer curPage, Integer pageSize) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" user.name as phoneNum, ");
        sb.append(" user.realname  as userRealName,  ");
        sb.append(" org.name  as userDuty,    ");
        sb.append(" org.flag02 as userDepartment, ");
        sb.append(" (select COUNT(1) from SysUser where recommendcode= user.name)as relationUserAmount, ");

        sb.append(" (select count(distinct userid) from t_payment p where p.cardStatus=1   ");
        sb.append("          and p.userid in (select id from SysUser where recommendcode= user.name) ) as tieCardAmount,   ");

        sb.append("  (select count(*) from t_reservation_info r ");
        sb.append("     where r.userid in (select id from SysUser where recommendcode= user.name)) as bespeakOrderAmount,");

        sb.append(" (select count(*) from t_order_info o where o.payStatus in (2,4)   ");
        sb.append("      and  o.financialPlannerId in (select id from SysUser where recommendcode= user.name)) as payOrderAmount,   ");

        sb.append("  (select sum(successAmount) from t_order_info o where o.payStatus in (2,4)   ");
        sb.append("    and  o.financialPlannerId in (select id from SysUser where recommendcode= user.name)) as payOrderMoney   ");

        sb.append(" FROM    ");
        sb.append(" SysUser user  ");
        sb.append(" left outer join t_organization org  on user.orgid =org.id   ");


        buildWhereSQL(userRealName, channel, sb);


        if(curPage != null && pageSize!=null){
            Integer offset = pageSize*(curPage-1);
            Integer rows = pageSize;
            sb.append(" limit "+offset+","+rows+" ");
        }

        return sb.toString();
    }

    private void buildWhereSQL(String userRealName, Integer channel, StringBuffer sb) {
        sb.append(" where 1=1   ");

        if (StringUtils.isNotEmpty(userRealName)) {
            sb.append(" and (user.RealName like '%" + userRealName + "%' or user.name like '%" + userRealName + "%')");
        }

        if (channel != null) {
            sb.append(" and  user.channel =  " + channel + " ");
        }
    }

    private String buildCountSQL(String userRealName, Integer channel) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" count(1) as total ");
        sb.append(" FROM    ");
        sb.append(" SysUser user  ");

        buildWhereSQL(userRealName, channel, sb);

        return sb.toString();
    }




}
